package com.Teilen.app.db.main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.Telien.app.request.object.DriverRequest;
import com.Telien.app.request.object.rideService;
import com.Telien.app.user.object.User;
import com.Telien.app.user.service.DriverRequestManagementService;
import com.Telien.app.utils.TelienConfig;
/*
class userInfo {
	int id;
	String userName;
	String password;
	String firstName;
	String lastName;
	String emailAddress;
	int phone;
	String role;
}
/*
class driverRequests{
	
	int id;
	String userID;
	String startCord;
	String endCord;
	String searchMileRadius;
	String endMileRadius;
	String passangerCount;
	String startTime;
	String endTime;
	
}

class requests{
	int id;
	String userID;
	Date date;
	boolean bit;
}
*/
class Log{
	int id;
	String log;
	Date date;
}

class Roles{
	int id;
	String Description;
	boolean active;
}


public class DBWrapper{
	private static final String dbUrlProp = "db.url";
	private static final String dbClassProp = "db.class";
	private static TelienConfig config;
	private static String dbUrl;
	private static String dbClass;
	
	public DBWrapper(){
		config = new TelienConfig();
		dbClass = config.getProperty(dbClassProp);
		dbUrl = config.getProperty(dbUrlProp);
	}
	
	public  int createUser(User user){
		int rc =0;
		
		// Build Query
		String query = null;
		query = "INSERT INTO Users (lastName,firstName,userName,password,phone,emailAddress,role) VALUES (" + 
										 "'" + user.getLastName() + "','" + user.getFirstName() + "','"
										 + user.getUserName() + "','" + user.getPassword() + "'," + user.getPhoneNumber() + ",'" + user.getEmailAddress()  
										 + "'," + user.getRole() + ");";
		
		// run Query
		rc = Query(query);
		
		return rc;
	}
	
	public  int deleteUser(String condition){
		
		int rc =0;
		// Build Query
		String query = null;
		query = "DELETE FROM Users WHERE " + condition;
		
		// run Query
		rc = Query(query);
		
		
		return rc;
	}
	
	public  int updateUser(String set, String condition){
		int rc =0;
		// Build Query
				String query = null;
				query = "UPDATE Users SET "+ set+ " WHERE "+ condition;
				
				// run Query
				rc = Query(query);
		
		return rc;
	}
	
	public  List<User> selectAllUsers(){	
		List<User> users = new ArrayList<User>();		
		// Build Query
		String query = null;
		query = "SELECT lastName,firstName,userName,password,phone,emailAddress,role,id FROM Users;";
		
		ResultSet result = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl);
			Statement stmt = con.createStatement();
			result = stmt.executeQuery(query);

			while (result.next()){
				
				String lastName = (String) result.getObject(1);
				String firstName = (String) result.getObject(2);
				String userName = (String) result.getObject(3);
				String password = (String) result.getObject(4);
				String phone = (String) result.getObject(5);
				String emailAddress = (String) result.getObject(6);
				Integer role = (Integer) result.getObject(7);
				Integer id = (Integer) result.getObject(8);
				
				User user = new User(id.toString(), userName, password, firstName, lastName, emailAddress, phone, role);
				users.add(user);
			}
			con.close();
		} //end try

		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}

		catch(SQLException e) {
			e.printStackTrace();
		}
		
		return users;
	}
	
	
	
	@SuppressWarnings("null")
	public  ResultSet selectUser(String columns, String condition){

		Statement stmt = null;
		ResultSet rs = null;
		
		// Build Query
				String query = null;
				query = "SELECT "+ columns+ " FROM User WHERE "+ condition;
				// run Query
				try {
					Class.forName("com.mysql.jdbc.Driver");
					Connection con = DriverManager.getConnection (dbUrl);
					stmt = con.createStatement();
					rs = stmt.executeQuery(query);
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return rs;
	}
	
	public String selectUser(String userId){
		Statement stmt = null;
		ResultSet rs = null;
		
		// Build Query
		String username1 = null;
		String query = null;
		query = "SELECT userName FROM Users WHERE id="+userId;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl);
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
			    username1 = rs.getString("userName");
			}
			con.close();
		}catch (SQLException e) {
					// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("Fetched Username:"+username1+" with ID"+userId);
		return username1;
	}
	
	private String getFormattedDate(Date dat){
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String formattedDate = df.format(dat);
		return formattedDate;
	}
	
	public int createRide(DriverRequest request){
		
		int rc =0;		
		
		// Build Query
				String query = null;
				query = "INSERT INTO DriverRequests (userID,startLat, startLong, endLat, endLong,searchMileRadius,endMileRadius,passanagerCount,startTime,endTime)"+
						"VALUES ("+ request.getUserUUID()+ ",'" + request.getStartCord().getLatitude() + "','" + request.getStartCord().getLongitude()
						+ "','" +request.getEndCord().getLatitude() + "','" +request.getEndCord().getLongitude() + "',"  
						+request.getStartMileRadius() + "," +request.getEndMileRadius() + "," +request.getPassanagerCount()+",'" 
						+ getFormattedDate(request.getStartTime())+"','" + getFormattedDate(request.getEndTime())+ "');";
				
				// run Query
				rc = Query(query);
				
		return rc;
	}
	
	
	public int createSession(rideService session){
		int rc =0;
		// Build Query
		String query = null;
		query = "INSERT INTO rideSession (rideSessionID, driverRequestUserID,requestUserID, sessionDate, activity, driverRequestID)"+
				"VALUES ('"+ session.getrideSessionID() + "','" + session.getdriverRequestUserID()+ "','" + session.getrequestUserID() + "','" + getFormattedDate(session.getsessionDate())
				+ "'," +session.getactivity() + ",'" + session.getRequest().getRequestUUID() + "');";
		System.out.println(query);
		// run Query
		rc = Query(query);
		
		return rc;
	}
	
	public int selectSessionsSize(){
		Statement stmt = null;
		ResultSet rs = null;
		
		// Build Query
		Integer size = null;
		String query = null;
		query = "SELECT COUNT(rideSessionID) FROM rideSession";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl);
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
			    size = Integer.parseInt(rs.getString("Count(rideSessionID)"));
			}
			con.close();
		}catch (SQLException e) {
					// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("rideSession size: " + size);
		return size + 1;
	}
	
	public int selectRequestSize(){
		Statement stmt = null;
		ResultSet rs = null;
		
		// Build Query
		Integer size = null;
		String query = null;
		query = "SELECT COUNT(id) FROM DriverRequests";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl);
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
			    size = Integer.parseInt(rs.getString("Count(id)"));
			}
			con.close();
		}catch (SQLException e) {
					// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("DriverRequest size: " + size);
		return size + 1;
	}
	
	
	//get session by customer userID
	@SuppressWarnings("null")
	public ResultSet customerRideRequests(String userID){
		Statement stmt = null;
		ResultSet rs = null;
		// Build Query
		String query = null;
		query = "select  distinct(rs.rideSessionID),dr.startLat, dr.endLat, dr.startTime, ur.firstName,ur.lastName,"
				+ " ur.phone,ur.emailAddress, rs.activity from DriverRequests dr join"+
				" rideSession rs on dr.userID="+userID+ " join Users ur on ur.userID ="+ userID;
		// run Query
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rs;
	}

	//get session by driver userID
	@SuppressWarnings("null")
	public ResultSet driverRideRequests(String userID){
		Statement stmt = null;
		ResultSet rs = null;
		// Build Query
		String query = null;
		query = "select distinct(rs.rideSessionID), ur.firstName,ur.lastName, ur.phone,ur.emailAddress,rs.activity"+
				" from Users ur Join rideSession rs on rs.requestUserID ="+ userID;
		// run Query
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rs;
	}
	
	//delete session by sessionID
	public int deleteRideSession(String sessionID){
		int rc=0;
		// Build Query
		String query = null;
		query = "delete from rideSession where rideSessionID ="+sessionID;
		// run Query
		rc = Query(query);
		
		return rc;
	}
	
	//update session activity bit
	public int updateRideSessionActivity(boolean activity, int rideSessionID){
		int rc=0;
		// Build Query
		String query = null;
		query = "UPDATE rideSession SET activity = " +activity+ " WHERE rideSessionID = '"+rideSessionID + "'";
		// run Query
		rc = Query(query);
		
		return rc;
	}
	
	
	public List<rideService> getSessions(){
		List<rideService> sessions = new ArrayList<rideService>();		
		// Build Query
		String query = null;
		query = "SELECT rideSessionID, driverRequestUserID, requestUserID, sessionDate, activity, driverRequestID FROM rideSession;";
		
		ResultSet result = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl);
			Statement stmt = con.createStatement();
			result = stmt.executeQuery(query);
			DriverRequestManagementService drms = new DriverRequestManagementService();

			while (result.next()){
				
				Integer sessionID = (Integer) result.getObject(1);
				Integer driverReqID = (Integer) result.getObject(2);
				Integer reqUserID = (Integer) result.getObject(3);
				Date sessionDate = (Date) result.getObject(4);
				Boolean activity = (Boolean) result.getObject(5);
				Integer requestId = (Integer) result.getObject(6);
				
				System.out.println(requestId);
				DriverRequest request = drms.getRequestAtId(requestId);
							
				rideService session = new rideService(sessionID, driverReqID, reqUserID, sessionDate, activity, request);
				sessions.add(session);
			}
			con.close();
		} //end try

		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}

		catch(SQLException e) {
			e.printStackTrace();
		}
		
		return sessions;
	}
	
	public void getColumnNames(String tableName){		
		// Build Query
		String query = null;
		query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '" + tableName + "';";
		
		ResultSet result = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl);
			Statement stmt = con.createStatement();
			result = stmt.executeQuery(query);

			while (result.next()){
				System.out.println(result.getObject(1));
				
			}
			con.close();
		} //end try

		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}

		catch(SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static int deleteRide(String condition){
		
		int rc =0;
		
		// Build Query
				String query = null;
				query = "DELETE FROM DriverRequests WHERE " + condition;
				
				// run Query
				rc = Query(query);
				
		return rc;
	}
	
	public static int updateRide(String set, String condition){
		int rc = 0;

		// Build Query
				String query = null;
				query = "UPDATE DriverRequests SET "+ set+ " WHERE "+ condition;
				
				// run Query
				rc = Query(query);
				
		return rc;
	}
	
	public static int selectRide(String columns, String condition){
		int rc =0;
		
		// Build Query
				String query = null;
				query = "SELECT "+ columns+ " FROM DriverRequests WHERE "+ condition;
				
				// run Query
				rc = Query(query);
		
		return rc;
	}
	
	public ArrayList<DriverRequest> getAllDriverRequests(){	
	// Build Query
		ArrayList<DriverRequest> requests= new ArrayList<DriverRequest>();
		String query = null;
		query = "SELECT * FROM DriverRequests;";
		ResultSet result = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl);
			Statement stmt = con.createStatement();
			result = stmt.executeQuery(query);
			System.out.println(result.toString());
			while (result.next()){
				
				int id =  (int)result.getInt(1);
				int userid = (int) result.getInt(2);
				String startLong =(String) result.getObject(3);
				String startLat = (String) result.getObject(4);
				String endLong = (String) result.getObject(5);
				String endLat = (String) result.getObject(6);
				int searchSR= (int) result.getInt(7);;
				int serachER= (int) result.getInt(8);
				int passCnt = (int) result.getInt(9);
				Timestamp sd = (Timestamp) result.getTimestamp(10);
				Timestamp ed = (Timestamp) result.getTimestamp(11);
				Date startDate = new Date(sd.getTime());
				Date endDate = new Date(ed.getTime());
				
				DriverRequest request = new DriverRequest(id,userid,startLat,startLong,endLat,endLong,searchSR,serachER,passCnt,startDate,endDate);
				requests.add(request);
			}
			con.close();
			return requests;
		} //end try
	
		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
	
		catch(SQLException e) {
			e.printStackTrace();
		}
		return requests;
	}
	
	public static int Query(String query) {
		String dbtime;
		int  rc =0;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl);
			Statement stmt = con.createStatement();
			System.out.println(query);
			rc = stmt.executeUpdate(query);
			con.close();
		} //end try

		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}

		catch(SQLException e) {
			e.printStackTrace();
		}
	
		return rc;
	}
	
	private ResultSet select(String query){
		ResultSet result = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl);
			Statement stmt = con.createStatement();
			result = stmt.executeQuery(query);
			con.close();
		} //end try

		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}

		catch(SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}

	// TESTING ONLY
	public int deleteAllSessions() {
		int rc =0;
		
		// Build Query
				String query = null;
				query = "DELETE FROM rideSession;";
				
				// run Query
				rc = Query(query);
				
		return rc;
		
	}
}
